﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Configuration;
using System.Data.OracleClient;
using System.Data ;

namespace CVSA.Suporte.Sinacor.BMFBovespa.Dados
{
    public class Users
    {
        private string connString = ConfigurationManager.ConnectionStrings["connStringSqlRemedy"].ToString();

        public string GetWebLoggedUser()
        {
            string user = string.Empty;
            user = HttpContext.Current.User.Identity.Name;
            return user;
        }

        public void RegisterVisit()
        {
            string user = string.Empty;
            user = HttpContext.Current.User.Identity.Name;

            string page = string.Empty;
            int i = HttpContext.Current.Request.Url.Segments.Count();
            page = HttpContext.Current.Request.Url.Segments[i - 1];

            
            string iSql = "INSERT INTO TB_SITESTAT (DTVISITA, NMUSUARIO, NMPAGINA) VALUES (:dtVisita, :nmUsuario, :nmPagina)";

            OracleConnection conn = new OracleConnection(connString);
            OracleCommand command = new OracleCommand();
            command.CommandType = System.Data.CommandType.Text;

            command.Parameters.AddWithValue("dtVisita", DateTime.Now);
            command.Parameters.AddWithValue("nmUsuario", user);
            command.Parameters.AddWithValue("nmPagina", page);

            command.CommandText = iSql;
            command.Connection = conn;
            conn.Open();
            command.ExecuteNonQuery();
            conn.Close();
        }

        public DataTable getSiteStats(DateTime InitialDate, DateTime EndDate)
        {

            string strQuery = @"select lower(nmusuario) as Login, nmpagina as Página, count(1) as Total
                            from tb_sitestat
                            where trunc(dtvisita) between  to_date(:initialDate, 'DD/MM/YYYY') and  to_date(:endDate, 'DD/MM/YYYY')
                            group by lower(nmusuario), nmpagina order by nmpagina";

            DataTable odt = new DataTable("Tb_SiteStats");
            OracleConnection conn = new OracleConnection(connString);

            OracleCommand command = new OracleCommand();
            command.CommandType = CommandType.Text;

            command.Parameters.AddWithValue("initialDate", InitialDate.ToShortDateString() );
            command.Parameters.AddWithValue("endDate", EndDate.ToShortDateString());

            command.CommandText = strQuery;
            command.Connection = conn;

            OracleDataAdapter da = new OracleDataAdapter(command);

            try
            {
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }

                da.Fill(odt);

                if (conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                }

            }
            catch (OracleException ex)
            {
                throw new Exception(ex.Message, ex.InnerException);
            }
            finally
            {

                da.Dispose();
                command.Dispose();
                conn.Dispose();
            }

            return odt;
        }

    }
}
